﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using eStock.Common;
using eStock.Properties;

namespace eStock.SQL
{
    class Sqlite : SQLDatabase
    {
        private string _dataSource;
        public SQLiteConnection Connection { get; private set; }


        public override bool Connect()
        {
            _dataSource = "eStock.db";
            return Open();
        }

        public override bool Connect(string server, string database, string user, string pass, int port = 3306)
        {
            _dataSource = database;
            if (!_dataSource.ToLower().EndsWith(".db"))
                _dataSource += ".db";
            return Open();
        }

        public override bool TryConnect(string server, string database, string user, string pass, int port = 3306)
        {
            try
            {
                Connection = new SQLiteConnection { ConnectionString = "Data Source=" + _dataSource };
                Connection.Open();
                Connection.Close();
                return true;
            }
            catch (Exception x)
            {
                LastError = new SQLError(x);
                return false;
            }
        }

        private bool Open()
        {
            try
            {
                Connection = new SQLiteConnection { ConnectionString = "Data Source=" + _dataSource };
                Connection.Open();
            }
            catch (Exception x)
            {
                LastError = new SQLError(x);
                throw LastError;
            }

            return true;
        }

        public override void Disconnect()
        {
            if(Connection != null)
            {
                Connection.Close();
                Connection.Dispose();
            }
        }

        public override SQLDataReader QueryRead(string sqlcommand)
        {
            if (Connection == null)
                throw new SQLNotConnectedException();

            try
            {
                SQLiteCommand command = new SQLiteCommand(Connection);
                LastCommand = sqlcommand;
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                command.CommandText = sqlcommand;
                return new SQLDataReader(command.ExecuteReader());
                
            }
            catch (Exception x)
            {
                LastError = new SQLError(x);
                throw LastError;
            }
        }

        public override long QueryWrite(string sqlcommand)
        {
            try
            {
                SQLiteCommand command = new SQLiteCommand(Connection);
                LastCommand = sqlcommand;
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                command.CommandText = sqlcommand;
                int rows = command.ExecuteNonQuery();
                
                command.Dispose();
                return rows;
            }
            catch (Exception x)
            {
                LastError = new SQLError(x);
                throw LastError;
            }
            finally
            {
                Connection.Close();
            }
        }

        public override long QueryWrite(string sqlcommand, Dictionary<string, object> parameters)
        {
            try
            {
                SQLiteCommand command = new SQLiteCommand(Connection);
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();

                if (sqlcommand.Contains("?"))
                    sqlcommand = sqlcommand.Replace("?", "@");

                
                LastCommand = sqlcommand;
                command.CommandText = sqlcommand;
                foreach (KeyValuePair<string, object> k in parameters)
                {
                    string s = k.Key.Replace("?", "@");
                    command.Parameters.AddWithValue(s, k.Value);
                }
                int rows = command.ExecuteNonQuery();
                command.Dispose();
                return rows;
            }
            catch (Exception x)
            {
                LastError = new SQLError(x);
                throw LastError;
            }
            finally
            {
                Connection.Close();
            }
        }

        public override Dictionary<string, object> FetchArray(SQLDataReader dbReader)
        {
            if (!dbReader.IsValid || !dbReader.Read())
            {
                Connection.Close();
                return null;
            }
            try
            {
                Dictionary<string, object> res = new Dictionary<string, object>();
                for (int i = 0; i < dbReader.FieldCount; i++)
                {
                    res.Add(dbReader.GetName(i), dbReader.GetValue(i));
                }
                return res;
            }
            catch (Exception x)
            {
                LastError = new SQLError(x);
                throw LastError;
            }
        }

        public override int FetchCount(SQLDataReader dbReader)
        {
            try
            {
                int i = 0;
                while (dbReader.Read()) i++;
                return i;
            }
            catch (Exception x)
            {
                LastError = new SQLError(x);
                throw LastError;
            }
            finally
            {
                Connection.Close();
            }
        }

        public override string DateFormat(string r, string f)
        {
            return "strftime('" + f + "', " + r + ")";
        }
    }
}
